package net.patterer.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.derby.jdbc.EmbeddedDataSource;

public class DatabaseTest1  
{

    /**
     * Gugu
     * @param args
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException
    {

        Connection conn = getDatabaseConnection();
        if (conn != null)
        {
            insertValue(conn);
        }
        queryValues(conn);
        conn.close();
    }

    public static Connection getDatabaseConnection()
    {

        Connection conn = null;
        EmbeddedDataSource ds = new EmbeddedDataSource();
        ds.setDatabaseName("Peter");
        try
        {
            conn = ds.getConnection();
        }
        catch (SQLException e)
        {
            ds.setCreateDatabase("create");
            try
            {
                conn = ds.getConnection();
                Statement stmt = conn.createStatement();
                stmt.execute("create table Images(ID int primary key generated always as identity, FILENAME varchar(256), PATHNAME varchar(512))");
                stmt.execute("create table Tags(TAG int primary key, IMAGEID int primary key, SEQNR int primary key WITH DEFAULT 1, VALUE varchar(1024))");
                stmt.close();
            }
            catch (SQLException e1)
            {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }
        return conn;

    }

    public static void insertValue(Connection conn)
    {
        Statement stmt;
        try
        {
            stmt = conn.createStatement();
            stmt.execute("insert into Images values (DEFAULT, 'Filename','Pathname')");
            stmt.execute("insert into Images values (DEFAULT, 'Filename2','Pathname2')");
            stmt.close();
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void queryValues(Connection conn)
    {
        Statement stmt;
        try
        {
            stmt = conn.createStatement();
            stmt.execute("select * from Images");
            ResultSet rs = stmt.getResultSet();
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next())
            {
                for (int i = 0; i < metaData.getColumnCount(); i++)
                {
                    System.out.print(rs.getString(i + 1));
                    System.out.print(" ");
                }
                System.out.println("");
            }
            rs.close();
            stmt.close();

        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}
